import sqlite3
import threading
import common_func
import logging

from common_func import print_with_location
from log_config import logger
from typing import Optional, List, Dict


class NetworkConfigDB:

    _instance = None
    _lock = threading.Lock()

    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            with cls._lock:
                if not cls._instance:
                    cls._instance = super(NetworkConfigDB, cls).__new__(cls)
        return cls._instance

    def __init__(
        self, db_path: str = "/usr/local/network-status/config/networkConfig.db"
    ):
        if hasattr(self, "_initialized") and self._initialized:
            return

        self.db_lock = threading.Lock()
        self.conn = sqlite3.connect(db_path, check_same_thread=False)
        self.cursor = self.conn.cursor()
        self._initialized = True

    def set_enable_status(self, config_id: int, enable: int) -> bool:
        print_with_location(f"config_id {config_id}")
        try:
            with self.db_lock:
                self.cursor.execute(
                    "UPDATE network_configurations SET enable = ? WHERE id = ?",
                    (1 if enable else 0, config_id),
                )
                self.conn.commit()
            return True
        except Exception as e:
            print_with_location("failed when updating the enable status", str(e))
            return False

    def set_connect_enable_status(self, config_id: int, enable: int) -> bool:
        print_with_location(f"config_id {config_id}")
        try:
            with self.db_lock:
                self.cursor.execute(
                    "UPDATE network_configurations SET enable_to_connect = ? WHERE id = ?",
                    (1 if enable else 0, config_id),
                )
                self.conn.commit()
            return True
        except Exception as e:
            print_with_location(
                "failed when updating the connect_enable status", str(e)
            )
            return False

    def get_enable_status(self, config_id: int) -> Optional[int]:
        print_with_location(f"config_id {config_id}")
        with self.db_lock:
            self.cursor.execute(
                "SELECT enable FROM network_configurations WHERE id = ?", (config_id,)
            )
            result = self.cursor.fetchone()
        print_with_location(f"result {result}")
        return int(result[0]) if result else 0

    def get_connect_enable_status(self, config_id: int) -> Optional[int]:
        print_with_location(f"config_id {config_id}")
        with self.db_lock:
            self.cursor.execute(
                "SELECT enable_to_connect FROM network_configurations WHERE id = ?",
                (config_id,),
            )
            result = self.cursor.fetchone()
        print_with_location(f"result {result}")
        return int(result[0]) if result else 0

    def update_wifi_config(
        self,
        config_id: int,
        ssid: str = None,
        password: str = None,
        security: str = None,
    ) -> bool:
        try:
            updates = []
            params = []

            if ssid is not None:
                updates.append("ssid = ?")
                params.append(ssid)
            if password is not None:
                updates.append("password = ?")
                params.append(password)
            if security is not None:
                updates.append("security = ?")
                params.append(security)

            if not updates:
                return True

            params.append(config_id)

            update_sql = f"""
            UPDATE wifi_specifics 
            SET {', '.join(updates)}
            WHERE config_id = ?
            """
            with self.db_lock:
                self.cursor.execute(update_sql, params)
                self.conn.commit()
            return self.cursor.rowcount > 0

        except sqlite3.Error as e:
            print_with_location(f"failed when updating the wifi config: {e}")
            return False

    def get_wifi_config(self, config_id: int) -> dict:
        try:
            select_sql = """
            SELECT config_id, ssid, password, security
            FROM wifi_specifics
            WHERE config_id = ?
            """
            with self.db_lock:
                self.cursor.execute(select_sql, (config_id,))
                row = self.cursor.fetchone()

            if row:
                return {
                    "config_id": row[0],
                    "ssid": row[1],
                    "password": row[2],
                    "security": row[3],
                }
            else:
                return {}

        except sqlite3.Error as e:
            print_with_location(f"failed when fetching the wifi config: {e}")
            return {}

    def get_network_type_id(self, name: str) -> Optional[int]:
        try:
            with self.db_lock:
                self.cursor.execute(
                    "SELECT id FROM network_types WHERE name = ?", (name,)
                )
                result = self.cursor.fetchone()
            return result[0] if result else None
        except sqlite3.Error as e:
            print_with_location(f"failed when searching for the network id: {e}")
            return None

    def close(self):
        with self.db_lock:
            self.conn.close()
